from utils import read_sql
import pandas as pd
import os
from utils import df_into_db


all_funding_rate_df = read_sql("select * from all_market_funding_rate", db_name="funding_rate")
all_cm_funding_rate_df = all_funding_rate_df[all_funding_rate_df.type == 'cm']
all_um_funding_rate_df = all_funding_rate_df[all_funding_rate_df.type == 'um']

cond = all_funding_rate_df['datetime'].apply(lambda x: str(x)[11:13] in ['00', '08', '16'])
all_funding_rate_df = all_funding_rate_df[cond]
cond = all_cm_funding_rate_df['datetime'].apply(lambda x: str(x)[11:13] in ['00', '08', '16'])
all_cm_funding_rate_df = all_cm_funding_rate_df[cond]
cond = all_um_funding_rate_df['datetime'].apply(lambda x: str(x)[11:13] in ['00', '08', '16'])
all_um_funding_rate_df = all_um_funding_rate_df[cond]


DATA_DIR = f'E:\\指标数据'  # 用于存放测试数据

temp_file_path = os.path.join(DATA_DIR, f'sth_momentum')
temp_file_name = os.path.join(temp_file_path, f'glassnode_btcusdt_hourly_ohlcv')
hourly_ohlcv = pd.read_excel(f'{temp_file_name}.xlsx', index_col='end_date')
hourly_ohlcv.index = hourly_ohlcv.index.map(lambda x: pd.to_datetime(x))
for index, rate_df in enumerate([all_funding_rate_df, all_cm_funding_rate_df, all_um_funding_rate_df]):
    df = rate_df.pivot(index='datetime', columns='symbol', values='funding_rate')
    analysis_df = pd.DataFrame()
    analysis_df['total_num'] = (~df.isnull()).sum(axis=1)
    analysis_df['neutral_num'] = (df == 0.0001).sum(axis=1)
    analysis_df['positive_num'] = (df > 0.0001).sum(axis=1)
    analysis_df['negative_num'] = (df < 0.0001).sum(axis=1)
    analysis_df['extreme_positive_num'] = (df > 0.005).sum(axis=1)
    analysis_df['extreme_negative_num'] = (df < -0.005).sum(axis=1)
    analysis_df['neutral_ratio'] = analysis_df['neutral_num'] / analysis_df['total_num']
    analysis_df['positive_ratio'] = analysis_df['positive_num'] / analysis_df['total_num']
    analysis_df['negative_ratio'] = analysis_df['negative_num'] / analysis_df['total_num']
    analysis_df['extreme_positive_ratio'] = analysis_df['extreme_positive_num'] / analysis_df['total_num']
    analysis_df['extreme_negative_ratio'] = analysis_df['extreme_negative_num'] / analysis_df['total_num']
    analysis_df['neutral_ratio_ma7'] = analysis_df['neutral_ratio'].rolling(7).mean()
    analysis_df['positive_ratio_ma7'] = analysis_df['positive_ratio'].rolling(7).mean()
    analysis_df['negative_ratio_ma7'] = analysis_df['negative_ratio'].rolling(7).mean()

    all_analysis_df = analysis_df.merge(hourly_ohlcv, how='left', left_index=True, right_index=True)
    if index == 0:
        all_analysis_df["type"] = "all"
    elif index == 1:
        all_analysis_df["type"] = "cm"
    elif index == 2:
        all_analysis_df["type"] = "um"
    else:
        raise NotImplementedError
    all_analysis_df = all_analysis_df.reset_index(drop=False)
    all_analysis_df = all_analysis_df.fillna(0)
    df_into_db(all_analysis_df, db_name="funding_rate", table_name="all_market_funding_rate_analysis")

